﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CarLoanSystem.Entity;

namespace CarLoanSystem.DAL
{
    public class CustomerDebtInfoDAL : DBBase
    {
        public static CustomerDebtItem LoadCusDept(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [CUSTOMER_DEBT_ID]
                                                          ,[DEBT_DETAIL]
                                                          ,[DEBT_AMOUNT]
                                                          ,[CUSTOMER_ID]
                                                          ,[CUSTOMER_DEBT_STATUS]
                                                      FROM [CAR_LOAN].[dbo].[CUSTOMER_DEBT] WHERE [CUSTOMER_DEBT_ID] = @CUSTOMER_DEBT_ID"
                                                       , DBBase.con);
                command.Parameters.AddWithValue("@CUSTOMER_DEBT_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    CustomerDebtItem item = new CustomerDebtItem();
                    item.CUSTOMER_DEBT_ID = Convert.ToInt32(dt.Rows[0]["CUSTOMER_DEBT_ID"].ToString());
                    item.CUSTOMER_ID = Convert.ToInt32(dt.Rows[0]["CUSTOMER_ID"].ToString());
                    item.DEBT_AMOUNT = Convert.ToInt32(dt.Rows[0]["DEBT_AMOUNT"].ToString());
                    item.DEBT_DETAIL = dt.Rows[0]["DEBT_DETAIL"].ToString();
                    item.CUSTOMER_DEBT_STATUS = dt.Rows[0]["CUSTOMER_DEBT_STATUS"].ToString();
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static decimal LoadSumDept(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT SUM([DEBT_AMOUNT]) AS SUM_DEBT_AMOUNT
                                                FROM [CAR_LOAN].[dbo].[CUSTOMER_DEBT]
                                                WHERE [CUSTOMER_ID] = @CUSTOMER_ID AND [CUSTOMER_DEBT_STATUS] = 'A'"
                                                       , DBBase.con);
                command.Parameters.AddWithValue("@CUSTOMER_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return Convert.ToDecimal(dt.Rows[0]["SUM_DEBT_AMOUNT"].ToString());
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        public static DataTable LoadCusDeptAll(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [CUSTOMER_DEBT_ID]
                                                          ,[DEBT_DETAIL]
                                                          ,[DEBT_AMOUNT]
                                                          ,[CUSTOMER_ID]   
                                                          ,[CUSTOMER_DEBT_STATUS]
                                                      FROM [CAR_LOAN].[dbo].[CUSTOMER_DEBT] WHERE [CUSTOMER_ID] = @CUSTOMER_ID"
                                                       , DBBase.con);
                command.Parameters.AddWithValue("@CUSTOMER_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static void Insert(CustomerDebtItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[CUSTOMER_DEBT]
                                                       ([DEBT_DETAIL]
                                                       ,[DEBT_AMOUNT]
                                                       ,[CUSTOMER_ID]
                                                       ,[CUSTOMER_DEBT_STATUS])
                                                 VALUES
                                                      ( @DEBT_DETAIL
                                                       ,@DEBT_AMOUNT
                                                       ,@CUSTOMER_ID
                                                       ,@CUSTOMER_DEBT_STATUS)", DBBase.con);

                command.Parameters.AddWithValue("@DEBT_DETAIL", item.DEBT_DETAIL);
                command.Parameters.AddWithValue("@DEBT_AMOUNT", item.DEBT_AMOUNT);
                command.Parameters.AddWithValue("@CUSTOMER_ID", item.CUSTOMER_ID);
                command.Parameters.AddWithValue("@CUSTOMER_DEBT_STATUS", item.CUSTOMER_DEBT_STATUS);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool Update(CustomerDebtItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[CUSTOMER_DEBT]
                                               SET [DEBT_DETAIL] = @DEBT_DETAIL
                                                  ,[DEBT_AMOUNT] = @DEBT_AMOUNT
                                                  ,[CUSTOMER_DEBT_STATUS] = @CUSTOMER_DEBT_STATUS
                                             WHERE ([CUSTOMER_DEBT_ID] = @CUSTOMER_DEBT_ID )", DBBase.con);

                command.Parameters.AddWithValue("@DEBT_DETAIL", item.DEBT_DETAIL);
                command.Parameters.AddWithValue("@DEBT_AMOUNT", item.DEBT_AMOUNT);
                command.Parameters.AddWithValue("@CUSTOMER_DEBT_ID", item.CUSTOMER_DEBT_ID);
                command.Parameters.AddWithValue("@CUSTOMER_DEBT_STATUS", item.CUSTOMER_DEBT_STATUS);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static void Delete(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[CUSTOMER_DEBT]
                                               SET [CUSTOMER_DEBT_STATUS] = 'I'
                                            WHERE DEBT_DETAIL = 'ค่ากู้รถยนต์ของบริษัท' AND CUSTOMER_ID = @CUSTOMER_ID", DBBase.con);
                command.Parameters.AddWithValue("@CUSTOMER_ID", id);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

//        public static void Delete(int id)
//        {
//            try
//            {
//                DBBase.ConncetDatabase();
//                SqlCommand command = new SqlCommand(@"DELETE FROM [CAR_LOAN].[dbo].[CUSTOMER_DEBT]
//                                                    WHERE DEBT_DETAIL = 'ค่ากู้รถยนต์ของบริษัท'
//                                                    AND CUSTOMER_ID = @CUSTOMER_ID", DBBase.con);
//                command.Parameters.AddWithValue("@CUSTOMER_ID", id);
//                int tmp = command.ExecuteNonQuery();
//                DBBase.DisConncetDatabase();
//            }
//            catch (Exception ex)
//            {
//                DBBase.DisConncetDatabase();
//                throw ex;
//            }
//        }
    }
}
